package com.yangjun.dao.impl.realtimeLiveAnalysis.realtimeSituation;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.yangjun.common.DVConstants;
import com.yangjun.dao.IRealTimeOverview;
import com.yangjun.dao.mybatis2.mapper.common.CommonMapper;
import com.yangjun.entity.realtimeliveAnalysis.RealTimeOverviewEntity;


/**
 * Created by Administrator on 2016/7/19.
 */
@Repository
public class RealTimeOverviewImpl implements IRealTimeOverview {
    @Autowired
    @Resource(name = "bikadJdbcTemplate")
    private JdbcTemplate jdbcTemplate;
    
    
	@Autowired
	public CommonMapper mapper;
    /**
     * -----------------------------------筛选平台查询（平台id,平台名称）---------------------------------------------------------------------------------------
     */
    public List<Map<String,Object>> findSelectPlatform(){
//    	List<String> siteidWeight = Arrays.asList(DVConstants.SITEID_WEIGHT);
    	List<String> siteidWeight = mapper.lastHaveSalesPlatform();
    	List<Map<String,Object>> resultMap = Lists.newArrayList();
    	Map<String,Map<String,Object>> idMap = Maps.newHashMap();
    	List<Map<String,Object>> siteMap =  jdbcTemplate.queryForList("SELECT V.VALUES_ID as ordersource,V.VALUES_NAME as sourcename "
    			+ "FROM BIDEV.DIM_KAD_BASE_VALUES V WHERE V.FLAG_ID=1");
    	StringBuffer sb = new StringBuffer();
    	for(Map<String,Object> map : siteMap){
			String id = String.valueOf(map.get("ordersource"));
			if("53".equals(id)){
				map.put("SOURCENAME", "E+药房(零售)");
			}else if("99902".equals(id)){
				map.put("SOURCENAME", "E+药房(批发)");
			}
			if("1".equals(id)){
				//客服代下也算PC网站
				map.put("ORDERSOURCE", "1,2");
			}
    		idMap.put(id, map);
		}
    	for(String str : siteidWeight){
    		if(idMap.get(str)!=null){
    			resultMap.add(idMap.get(str));
        		idMap.remove(str);
    		}
    	}
    	for(Map.Entry<String,Map<String,Object>> map:  idMap.entrySet()){
    		sb.append(map.getKey()).append(",");
    	}
    	Map<String,Object> otherMap = new HashMap<String,Object>();
    	otherMap.put("SOURCENAME","其他");
    	otherMap.put("ORDERSOURCE",sb.toString().substring(0,sb.toString().length()-1));
    	resultMap.add(otherMap);
    	return resultMap;
    }
   
    public List<RealTimeOverviewEntity>  findRealTimeOverview(List<String> ordersources,String yestoday,String today,String contrastday){
       	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	    MapSqlParameterSource parameters = new MapSqlParameterSource();
	    parameters.addValue("ordersources", ordersources);
    	StringBuffer sql = new StringBuffer("Select" +
				" to_char(a.data_date, 'YYYY-MM-DD') as 日期  ," +//trunc(a.data_date) as 日期
				" sum(a.order_amount) as 销售额," +
				" sum(a.order_cnt) as 订单数," +
				" SUM(A.CUS_CNT) AS 购买用户数, " +
				" sum(a.nopay_amount) as 待付款金额," +
				" SUM(A.NOPAY_ORDER_CNT) AS 待付款订单数," +
				" case when sum(a.order_cnt)=0 then 0 else trunc(sum(a.order_amount) / sum(a.order_cnt)) end AS 客单价," +
				" SUM(a.SYN_CONSIGN_CNT) AS 同步到仓库包裹数," +
				" SUM(A.CONSIGN_OUT_CNT) AS 出库包裹数, " +
				" SUM(a.NOSYN_ORDER_CNT) AS 未同步订单数," +
				" SUM(a.NOPICK_CONSIGN_CNT) AS 同步未拣货包裹数," +
				" SUM(a.REFUND_ORDER_CNT) AS 申请退款订单数," +
				" SUM(a.REFUND_AMOUNT) AS 申请退款金额," +
				" SUM(a.DOWN_REFUND_AMOUNT) AS 已处理退款金额," +
				" SUM(a.DOWN_REFUND_CNT) AS 已处理退款订单数," +
				" SUM(a.COLLECT_CONSIGN_CNT) AS 揽收包裹数," +
				" SUM(a.NOCOLLECT_CONSIGN_CNT) AS 未揽收包裹数," +
				" SUM(a.DEAL_CONSIGN_CNT) AS 仓库作业中的包裹数," +
				" max(a.insert_time)  AS 插入时间 " +
				" From bikad.DV_DW_KAD_LIVE_OVERVIEW a " +
				" where data_date in (date'" + yestoday + "', date'" + today + "', date'" + contrastday + "') and ordersource != 9999 "); 
				if(ordersources.size()>0){
		    		sql.append(" and ordersource in (:ordersources)");
		    	}
				sql.append(" GROUP BY a.data_date order by a.data_date");
				
//				System.out.println("findRealTimeOverview--"+sql.toString());
				List<RealTimeOverviewEntity> realTimeOverviewEntityList = namedParameterJdbcTemplate.query(sql.toString(), parameters, new RealTimeOverviewEntityMapper());
        return realTimeOverviewEntityList;
    }
    class RealTimeOverviewEntityMapper implements RowMapper<RealTimeOverviewEntity>{
        public RealTimeOverviewEntity mapRow(ResultSet resultSet, int i) throws SQLException {
            RealTimeOverviewEntity realTimeOverviewEntity = new RealTimeOverviewEntity();
            realTimeOverviewEntity.setDate(resultSet.getString("日期"));
            realTimeOverviewEntity.setOrderAmount(resultSet.getInt("销售额"));
            realTimeOverviewEntity.setOrderCnt(resultSet.getInt("订单数"));
            realTimeOverviewEntity.setCusCnt(resultSet.getInt("购买用户数"));
            realTimeOverviewEntity.setNopayAmount(resultSet.getInt("待付款金额"));
            realTimeOverviewEntity.setNopayOrderCnt(resultSet.getInt("待付款订单数"));
            realTimeOverviewEntity.setPerCustomerTransaction(resultSet.getInt("客单价"));
            realTimeOverviewEntity.setSynConsignCnt(resultSet.getInt("同步到仓库包裹数"));
            realTimeOverviewEntity.setConsignOutCnt(resultSet.getInt("出库包裹数"));
            realTimeOverviewEntity.setNosynOrderCnt(resultSet.getInt("未同步订单数"));
            realTimeOverviewEntity.setNopickConsignCnt(resultSet.getInt("同步未拣货包裹数"));
            realTimeOverviewEntity.setRefundOrderCnt(resultSet.getInt("申请退款订单数"));
            realTimeOverviewEntity.setRefundAmount(resultSet.getInt("申请退款金额"));
            realTimeOverviewEntity.setDownRefundAmount(resultSet.getInt("已处理退款金额"));
            realTimeOverviewEntity.setDownRefundCnt(resultSet.getInt("已处理退款订单数"));
            realTimeOverviewEntity.setCollectConsignCnt(resultSet.getInt("揽收包裹数"));
            realTimeOverviewEntity.setNocollectConsignCnt(resultSet.getInt("未揽收包裹数"));
            realTimeOverviewEntity.setDealConsignCnt(resultSet.getInt("仓库作业中的包裹数"));
            realTimeOverviewEntity.setInsertTime(resultSet.getTimestamp("插入时间"));
            return realTimeOverviewEntity;
        }
    }
    //获取销售额
    public List<Map<String,Object>> findOrderAmount(String today,String contrastday,List<String> ordersources) {
    	StringBuffer sql = new StringBuffer("select to_char(d.DATA_DATE,'YYYY-MM-DD') as dataDate,d.DATA_HOUR as dataHour,round(sum(order_amount),0) as orderAmount from DV_DW_KAD_LIVE_OVERVIEW d where d.DATA_DATE in (date'"+today+"',date'"+contrastday+"') ");
    	if(ordersources.size()>0){
    		sql.append(" and d.ordersource in (:ordersources)");
    	}
    	sql.append(" and   d.ORDERSOURCE !='9999' group by d.DATA_DATE,d.DATA_HOUR order by d.DATA_DATE,d.DATA_HOUR");
    	
    	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	    MapSqlParameterSource parameters = new MapSqlParameterSource();
	    parameters.addValue("ordersources", ordersources);
    	List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql.toString(), parameters);
    	return rows;
    }
    //获取订单数
    public List<Map<String,Object>> findOrderCnt(String today,String contrastday,List<String> ordersources) {
    	StringBuffer sql = new StringBuffer("select to_char(d.DATA_DATE,'YYYY-MM-DD') as dataDate,d.DATA_HOUR as dataHour,round(sum(order_cnt),0) as orderCnt from DV_DW_KAD_LIVE_OVERVIEW d where d.DATA_DATE in (date'"+today+"',date'"+contrastday+"') ");
    	if(ordersources.size()>0){
    		sql.append(" and d.ordersource in (:ordersources)");
    	}
    	sql.append(" and   d.ORDERSOURCE !='9999' group by d.DATA_DATE,d.DATA_HOUR order by d.DATA_DATE,d.DATA_HOUR");
    	
    	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	    MapSqlParameterSource parameters = new MapSqlParameterSource();
	    parameters.addValue("ordersources", ordersources);
    	List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql.toString(), parameters);
    	return rows;
    }
    //获取出库包裹数
    public List<Map<String, Object>> findConsignOutCnt(String today,String contrastday,List<String> ordersources) {
     	StringBuffer sql = new StringBuffer("select to_char(d.DATA_DATE,'YYYY-MM-DD') as dataDate,d.DATA_HOUR as dataHour,round(sum(CONSIGN_OUT_CNT),0) as consignOutCnt from DV_DW_KAD_LIVE_OVERVIEW d where d.DATA_DATE in (date'"+today+"',date'"+contrastday+"') ");
    	if(ordersources.size()>0){
    		sql.append(" and d.ordersource in (:ordersources)");
    	}
    	sql.append(" and   d.ORDERSOURCE !='9999' group by d.DATA_DATE,d.DATA_HOUR order by d.DATA_DATE,d.DATA_HOUR");
     	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	    MapSqlParameterSource parameters = new MapSqlParameterSource();
	    parameters.addValue("ordersources", ordersources);
    	List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql.toString(), parameters);
    	return rows;
    }
    //获取仓库实时出库
    public List<Map<String, Object>> findConsignDeliverCnt(String today,String contrastday,List<String> ordersources) {
     	StringBuffer sql = new StringBuffer("select to_char(d.DATA_DATE,'YYYY-MM-DD') as dataDate,d.DATA_HOUR as dataHour,round(sum(CONSIGN_DELIVER_CNT),0) as consignDeliverCnt from DV_DW_KAD_LIVE_OVERVIEW d where d.DATA_DATE in (date'"+today+"',date'"+contrastday+"') ");
    	if(ordersources.size()>0){
    		sql.append(" and d.ordersource in (:ordersources)");
    	}
    	sql.append(" and   d.ORDERSOURCE !='9999' group by d.DATA_DATE,d.DATA_HOUR order by d.DATA_DATE,d.DATA_HOUR");
     	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	    MapSqlParameterSource parameters = new MapSqlParameterSource();
	    parameters.addValue("ordersources", ordersources);
    	List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql.toString(), parameters);
    	return rows;
    }
    //申请退款订单数
    public List<Map<String, Object>> findRefundOrderCnt(String today,String contrastday,List<String> ordersources) {
    	StringBuffer sql = new StringBuffer("select to_char(d.DATA_DATE,'YYYY-MM-DD') as dataDate,d.DATA_HOUR as dataHour,round(sum(REFUND_ORDER_CNT),0) as refundOrderCnt from DV_DW_KAD_LIVE_OVERVIEW d where d.DATA_DATE in (date'"+today+"',date'"+contrastday+"') ");
    	if(ordersources.size()>0){
    		sql.append(" and d.ordersource in (:ordersources)");
    	}
    	sql.append(" and   d.ORDERSOURCE !='9999' group by d.DATA_DATE,d.DATA_HOUR order by d.DATA_DATE,d.DATA_HOUR");
     	
    	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	    MapSqlParameterSource parameters = new MapSqlParameterSource();
	    parameters.addValue("ordersources", ordersources);
    	List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql.toString(), parameters);
    	return rows;
    }
    //已处理退款订单数 
    public List<Map<String, Object>> findDownRefundCnt(String today,String contrastday,List<String> ordersources) {
    	StringBuffer sql = new StringBuffer("select to_char(d.DATA_DATE,'YYYY-MM-DD') as dataDate,d.DATA_HOUR as dataHour,round(sum(DOWN_REFUND_CNT),0) as downRefundCnt from DV_DW_KAD_LIVE_OVERVIEW d where d.DATA_DATE in (date'"+today+"',date'"+contrastday+"') ");
    	if(ordersources.size()>0){
    		sql.append(" and d.ordersource in (:ordersources)");
    	}
    	sql.append(" and   d.ORDERSOURCE !='9999' group by d.DATA_DATE,d.DATA_HOUR order by d.DATA_DATE,d.DATA_HOUR");
     	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	    MapSqlParameterSource parameters = new MapSqlParameterSource();
	    parameters.addValue("ordersources", ordersources);
    	List<Map<String,Object>> rows = namedParameterJdbcTemplate.queryForList(sql.toString(), parameters);
    	return rows;
    }
    
    //获取平台销售额(柱状图)
    public Map<String,Object> findSalesAmountByPlatform(String date,List<String> ordersources){
        Map<String,Object> resultMap = Maps.newHashMap();
        StringBuffer sql = new StringBuffer("select trunc(sum(ORDER_AMOUNT)) as 金额,d.SOURCENAME as 平台 from DV_DW_KAD_LIVE_OVERVIEW d where d.DATA_DATE=date'"+date+"' ");
     	if(ordersources.size()>0){
    		sql.append(" and d.ordersource in (:ordersources)");
    	}
        sql.append("group by d.SOURCENAME order by 金额 desc");
     	NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
	    MapSqlParameterSource parameters = new MapSqlParameterSource();
	    parameters.addValue("ordersources", ordersources);
        List<Map<String,Object>> queryList = namedParameterJdbcTemplate.queryForList(sql.toString(), parameters);
        List<String> amountList = Lists.newArrayList();
        List<String> platformList = Lists.newArrayList();
        for(int i=0,len=queryList.size();i<len;i++){
        	String amount = String.valueOf(queryList.get(i).get("金额"));
        	if(!"0".equals(amount)){
        		amountList.add(amount);
                String platformName = String.valueOf(queryList.get(i).get("平台"));
                if("IPhone".equals(platformName)){
                	platformName = "IOS";
                }
                platformList.add(platformName);
        	}
        }
        resultMap.put("value",amountList);
        resultMap.put("platform",platformList);
        return  resultMap;
    }
	/**
	 * 缺货SKU数  影响销售额 
	 * @param inputDate
	 * @return
	 */
	public Map<String,Object> outstockwaresSummary(){
		Map resultMap = Maps.newHashMap();
		StringBuffer sql = new StringBuffer(" select a.data_date as data_date,count(distinct warecode) as sku,sum(a.impact_amount) as outstock_order_amt  "+
        " from dv_dw_kad_live_ware_outstock a WHERE  (a.data_date=trunc(sysdate) or a.data_date=trunc(sysdate)-1) group by a.data_date ");
		List<Map<String,Object>> resultList = jdbcTemplate.query(sql.toString(), new RowMapper<Map<String, Object>>(){
		@Override
		public Map<String,Object> mapRow(ResultSet rs, int n) throws SQLException {
				Map<String,Object> resultMap = Maps.newHashMap();
				resultMap.put("data_date", rs.getDate("data_date"));
				resultMap.put("sku", rs.getInt("sku"));//缺货sku数
				resultMap.put("outstockOrderAmt", rs.getInt("outstock_order_amt"));//影响销售额
				return resultMap;
			}
		});
		for(Map<String,Object> map : resultList){
			resultMap.put(map.get("data_date").toString(), map);
			System.out.println("===== key = " + map.get("data_date").toString());
			System.out.println("map = " + map);
		}
		return resultMap;
	}
	
	/**
	 * 采购供应 商品详情
	 */
	@Override
	public List<Map<String, Object>> wareOutstockDetails(String warecode) {
		List<Object> params = new ArrayList<Object>();
		 
		 StringBuffer sql = new StringBuffer("select to_char(t.data_date, 'YYYY-MM-DD') as data_date,sum(t.saleqty) as saleqty "
				 +"        from visc_sigle_ware t "
				 +"        where t.data_date between  trunc(sysdate-30) and trunc(sysdate) ");
		 
		 
		 if(StringUtils.isNotBlank(warecode)){
			 sql.append("and t.warecode=? ");
			 params.add(warecode);
		 }
		 sql.append("group by to_char(t.data_date, 'YYYY-MM-DD') order by data_date");
		 return jdbcTemplate.queryForList(sql.toString(), params.toArray());
	}
}
